﻿using System.ServiceModel;
using Dapper;
using System;
using System.Linq;
using SilenthinkContract;
using System.Collections.Generic;
using SilenthinkContract.Domain;

namespace SilenthinkService
{
    public class AuctionService : ServiceBase, IAuctionService
    {
        #region Auction

        public bool CreateAuction(Auction o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Auction.Table.Auction)
                .Field(
                    "([Name],[ProductId],[ProductVariantId],[Price],[BidPrice],[UserId],[NickName],[OnlineTime],[BeginTime],[Interval],[ExpectedTime],[ActualTime],[Status],[CreatedBy],[CreatedOn])VALUES(@Name,@ProductId,@ProductVariantId,@Price,@BidPrice,@UserId,@NickName,@OnlineTime,@BeginTime,@Interval,@ExpectedTime,@TransactionTime,@Status,@CreatedBy,@CreatedOn)")
                .AddParameters(
                    new
                        {
                            o.Name,
                            o.ProductId,
                            o.ProductVariantId,
                            Price = o.PerPrice,
                            o.BidPrice,
                            o.UserId,
                            o.NickName,
                            o.OnlineTime,
                            BeginTime = o.StartTime,
                            o.Interval,
                            o.ExpectedTime,
                            o.TransactionTime,
                            o.Status,
                            o.CreatedBy,
                            o.CreatedOn
                        });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateAuction(Auction o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Auction.Table.Auction)
                .Field("[Name]=@Name", new {o.Name})
                .Field("[ProductId]=@ProductId", new {o.ProductId})
                .Field("[ProductVariantId]=@ProductVariantId", new {o.ProductVariantId})
                .Field("[Price]=@Price", new {Price = o.PerPrice})
                .Field("[BidPrice]=@BidPrice", new {o.BidPrice})
                .Field("[UserId]=@UserId", new {o.UserId})
                .Field("[NickName]=@NickName", new {o.NickName})
                .Field("[OnlineTime]=@OnlineTime", new {o.OnlineTime})
                .Field("[BeginTime]=@BeginTime", new {BeginTime = o.StartTime})
                .Field("[Interval]=@Interval", new {o.Interval})
                .Field("[ExpectedTime]=@ExpectedTime", new {o.ExpectedTime})
                .Field("[TransactionTime]=@TransactionTime", new {o.TransactionTime})
                .Field("[Status]=@Status", new {o.Status})
                .Where("[Id] = @Id", new {o.Id});

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteAuction(long id)
        {
            var builder = new SqlBuilder()
                .Table(Database.Auction.Table.Auction)
                .Where("[Id] = @Id", new {id});

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Execute(connection, template) == 1;
            }
        }

        public string Buy(long id)
        {
            return string.Empty;
        }

        public int SumBid(long userId, long auctionId, DateTime? begin = null)
        {
            var builder = new SqlBuilder()
                .Field("ISNULL(SUM([Price]),0) as [Sum]")
                .Table(Database.Auction.Table.Bidding)
                .Where("[UserId] = @UserId", new {userId})
                .Where("[AuctionId] = @AuctionId", new {auctionId});
            if (begin.HasValue)
            {
                builder.Where("[CreatedOn]>=@CreatedOn", new {createdOn = begin});
            }

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Query<int>(connection, template).FirstOrDefault();
            }
        }

        public Auction GetAuction(long id)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Auction.Table.Auction)
                .Where("[Id]=@Id", new {id});

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Query<Auction>(connection, template).FirstOrDefault();
            }
        }

        public IList<Auction> GetAllAuction()
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Auction.Table.Auction);

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Query<Auction>(connection, template).ToList();
            }
        }

        public PageList<Auction> GetPagedAuction(int index = 1, int size = 20)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Auction.Table.Auction)
                .OrderBy("id desc")
                .AddParameters(new {begin = (index - 1)*size + 1, end = index*size});
            /*
            .Where("");
            */

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return PageList<Auction>(connection, template);
            }
        }

        #endregion

        #region BiddingRecord

        public PageList<Bid> GetPagedBidding(int index = 1, int size = 20, long? auctionId = null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Auction.Table.Bidding)
                .OrderBy("id desc")
                .AddParameters(new {begin = (index - 1)*size + 1, end = index*size});
            /*
            .Where("");
            */

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return PageList<Bid>(connection, template);
            }
        }

        #endregion

        #region Auction.Order

        public bool CreateAuctionOrder(AuctionOrder o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Auction.Table.AuctionOrder)
                .Field(
                    "([UserId],[Category],[AuctionId],[Grade],[Name],[PictureSrc],[Description],[BidPrice],[BuyPrice],[MarketPrice],[Quantity],[Deduction],[AdditionalCharge],[Total],[IntegralExchange],[ShippingMethod],[PaymentMethod],[PaidDate],[ShippingDate],[DeliveryDate],[OrderStatus],[ShippingStatus],[PaymentStatus],[Expiration],[AreaCode],[Receiver],[Phone],[Mobile],[Province],[City],[Area],[Address],[PostalCode],[InvoiceCategory],[InvoiceTitle],[InvoiceContent],[IpAddress],[Mesage],[Extra],[CreatedBy],[CreatedOn])VALUES(@UserId,@Category,@AuctionId,@Grade,@Name,@PictureSrc,@Description,@BidPrice,@BuyPrice,@MarketPrice,@Quantity,@Deduction,@AdditionalCharge,@Total,@IntegralExchange,@ShippingMethod,@PaymentMethod,@PaidDate,@ShippingDate,@DeliveryDate,@OrderStatus,@ShippingStatus,@PaymentStatus,@Expiration,@AreaCode,@Receiver,@Phone,@Mobile,@Province,@City,@Area,@Address,@PostalCode,@InvoiceCategory,@InvoiceTitle,@InvoiceContent,@IpAddress,@Mesage,@Extra,@CreatedBy,@CreatedOn)")
                .AddParameters(
                    new
                        {
                            o.UserId,
                            o.Category,
                            o.AuctionId,
                            o.Grade,
                            o.Name,
                            o.PictureSrc,
                            o.Description,
                            o.BidPrice,
                            o.BuyPrice,
                            o.MarketPrice,
                            o.Quantity,
                            o.Deduction,
                            o.AdditionalCharge,
                            o.Total,
                            o.IntegralExchange,
                            o.ShippingMethod,
                            o.PaymentMethod,
                            o.PaidDate,
                            o.ShippingDate,
                            o.DeliveryDate,
                            o.OrderStatus,
                            o.ShippingStatus,
                            o.PaymentStatus,
                            o.Expiration,
                            o.AreaCode,
                            o.Receiver,
                            o.Phone,
                            o.Mobile,
                            o.Province,
                            o.City,
                            o.Area,
                            o.Address,
                            o.PostalCode,
                            o.InvoiceCategory,
                            o.InvoiceTitle,
                            o.InvoiceContent,
                            o.IpAddress,
                            o.Mesage,
                            o.Extra,
                            o.CreatedBy,
                            o.CreatedOn
                        });
            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateAuctionOrder(AuctionOrder o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Auction.Table.AuctionOrder)
                .Field("[BidPrice]=@BidPrice", new {o.BidPrice})
                .Field("[BuyPrice]=@BuyPrice", new {o.BuyPrice})
                .Field("[MarketPrice]=@MarketPrice", new {o.MarketPrice})
                .Field("[Quantity]=@Quantity", new {o.Quantity})
                .Field("[Deduction]=@Deduction", new {o.Deduction})
                .Field("[AdditionalCharge]=@AdditionalCharge", new {o.AdditionalCharge})
                .Field("[Total]=@Total", new {o.Total})
                .Field("[IntegralExchange]=@IntegralExchange", new {o.IntegralExchange})
                .Field("[ShippingMethod]=@ShippingMethod", new {o.ShippingMethod})
                .Field("[PaymentMethod]=@PaymentMethod", new {o.PaymentMethod})
                .Field("[PaidDate]=@PaidDate", new {o.PaidDate})
                .Field("[ShippingDate]=@ShippingDate", new {o.ShippingDate})
                .Field("[DeliveryDate]=@DeliveryDate", new {o.DeliveryDate})
                .Field("[OrderStatus]=@OrderStatus", new {o.OrderStatus})
                .Field("[ShippingStatus]=@ShippingStatus", new {o.ShippingStatus})
                .Field("[PaymentStatus]=@PaymentStatus", new {o.PaymentStatus})
                .Field("[Expiration]=@Expiration", new {o.Expiration})
                .Field("[AreaCode]=@AreaCode", new {o.AreaCode})
                .Field("[Receiver]=@Receiver", new {o.Receiver})
                .Field("[Phone]=@Phone", new {o.Phone})
                .Field("[Mobile]=@Mobile", new {o.Mobile})
                .Field("[Province]=@Province", new {o.Province})
                .Field("[City]=@City", new {o.City})
                .Field("[Area]=@Area", new {o.Area})
                .Field("[Address]=@Address", new {o.Address})
                .Field("[PostalCode]=@PostalCode", new {o.PostalCode})
                .Field("[InvoiceCategory]=@InvoiceCategory", new {o.InvoiceCategory})
                .Field("[InvoiceTitle]=@InvoiceTitle", new {o.InvoiceTitle})
                .Field("[InvoiceContent]=@InvoiceContent", new {o.InvoiceContent})
                .Field("[IpAddress]=@IpAddress", new {o.IpAddress})
                .Field("[Mesage]=@Mesage", new {o.Mesage})
                .Field("[Extra]=@Extra", new {o.Extra})
                .Where("[Id] = @Id And [UserId]=UserId", new { o.Id, o.UserId });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteAuctionOrder(string id)
        {
            var builder = new SqlBuilder()
                .Table(Database.Auction.Table.AuctionOrder)
                .Where("[Id] = @Id", new {id});

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Execute(connection, template) == 1;
            }
        }


        public AuctionOrder GetAuctionOrder(string id, int? userId=null)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Auction.Table.AuctionOrder)
                .Where("[Id] = @Id", new {id});
            
            if(userId.HasValue&&userId.Value>0)
            {
                builder.Where("[UserId] = @UserId", new { userId });
            }
            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Query<AuctionOrder>(connection, template).FirstOrDefault();
            }
        }

        public IList<AuctionOrder> GetAllAuctionOrder()
        {
            throw new NotImplementedException();
        }

        public PageList<AuctionOrder> GetPagedAuctionOrder(int index = 1, int size = 20,int? userId=0,AuctionGrade? grade=null,AuctionStatus? status=null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Auction.Table.AuctionOrder)
                .OrderBy("id desc")
                .AddParameters(new {begin = (index - 1)*size + 1, end = index*size});

            if(userId.HasValue&&userId.Value>0)
            {
                builder.Where("[UserId]=@UserId", new {userId});
            }
            if (grade.HasValue && grade.Value > 0)
            {
                builder.Where("[Grade]=@Grade", new { grade });
            }
            if (status.HasValue && status.Value > 0)
            {
                builder.Where("[Status]=@Status", new { status });
            }
            
            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return PageList<AuctionOrder>(connection, template);
            }
        }

        #endregion

        #region Implementation of IService

        public int GetCachedSQLCount()
        {
            return SqlMapper.GetCachedSQLCount();
        }
        public List<Tuple<string, string, int>> GetCachedSQL()
        {
            return SqlMapper.GetCachedSQL().ToList();
        }
        public void PurgeQueryCache()
        {
            SqlMapper.PurgeQueryCache();
        }
        #endregion

    }
}